上一篇已經大致完成子報表模板的設置，今天來看看後端要怎麼處理資料。
這次的情境是每個科系的各個課程成績平均，科系作為主報表的資料，各個課程以及成績平均作為子報表的資料，是一個對多個，我個人會處理為主報表List中再放入子報表List，可以明確知道這個科系包含哪些課程成績資料。
SQL查詢資料映射到Dto中。
@Data
@NoArgsConstructor
@AllArgsConstructor
public class StudentCourseScoreDto {
private Integer studentId;
private String firstName;
private String lastName;
private String gender;
private String grade;
private Integer departmentId;
private String departmentName;
private String departmentDesc;
private Integer courseId;
private String courseName;
private String courseDesc;
private String credit;
private Integer scoreId;
private Integer score;
private Date testDate;
}
@Override
public List<StudentCourseScoreDto> getStudentCourseScoreData() {
QStudentEntity qStudent = QStudentEntity.studentEntity;
QDepartmentEntity qDepartment = QDepartmentEntity.departmentEntity;
QCourseEntity qCourse = QCourseEntity.courseEntity;
QScoreEntity qScore = QScoreEntity.scoreEntity;
return queryFactory.select(Projections
.bean(StudentCourseScoreDto.class,
qStudent.studentId, qStudent.firstName, qStudent.lastName,
qStudent.gender, qStudent.grade, qDepartment.departmentId,
qDepartment.departmentName, qDepartment.departmentDesc,
qCourse.courseId, qCourse.courseName, qCourse.courseDesc,
qCourse.credit, qScore.scoreId, qScore.score, qScore.testDate))
.from(qStudent)
.innerJoin(qDepartment)
.on(qStudent.departmentId.eq(qDepartment.departmentId))
.innerJoin(qScore)
.on(qStudent.studentId.eq(qScore.studentId))
.innerJoin(qCourse)
.on(qScore.courseId.eq(qCourse.courseId))
.fetch();
}
List<StudentCourseScoreDto> studentCourseScoreDtoList =
Optional.of(jasperReportDemoDao.getStudentCourseScoreData())
.orElse(new ArrayList<>());
Map<Integer, List<StudentCourseScoreDto>> studentCourseScoreDtoMap =
studentCourseScoreDtoList.stream()
.collect(Collectors.groupingBy(StudentCourseScoreDto::getDepartmentId));
@Data
@NoArgsConstructor
@AllArgsConstructor
public class DepartmentCourseScoreAverageReportModel {
private String departmentId;
private String departmentDesc;
// 子報表
private List<SubReportAverageScoreModel> subReportAverageScoreModelList;
}
子報表資料放入課程名稱中文courseDesc與該課程平均成績averageScore。
@Data
@NoArgsConstructor
@AllArgsConstructor
public class SubReportAverageScoreModel {
private String courseDesc;
private BigDecimal averageScore;
}
// 主報表、子報表物件清單
List<DepartmentCourseScoreAverageReportModel> mainReportModelList = new ArrayList<>();
for (Map.Entry<Integer, List<StudentCourseScoreDto>> entry : studentCourseScoreDtoMap.entrySet()) {
Integer departmentId = entry.getKey();
List<StudentCourseScoreDto> entryValue = entry.getValue();
String departmentDesc = entryValue.get(0).getDepartmentDesc();
// 用課程分群
Map<Integer, List<StudentCourseScoreDto>> courseScoreDtoMap = entryValue.stream()
.collect(Collectors.groupingBy(StudentCourseScoreDto::getCourseId));
// 子報表清單
List<SubReportAverageScoreModel> subReportModelList = new ArrayList<>();
// 計算課程平均
for (Map.Entry<Integer, List<StudentCourseScoreDto>> courseEntry : courseScoreDtoMap.entrySet()) {
List<StudentCourseScoreDto> courseValue = courseEntry.getValue();
String courseDesc = courseValue.get(0).getCourseDesc();
BigDecimal totalScore = new BigDecimal(courseValue.stream().mapToInt(StudentCourseScoreDto::getScore).sum());
BigDecimal averageScore = totalScore.divide(new BigDecimal(courseValue.size()), 2, RoundingMode.HALF_UP);
// 課程名稱、平均成績放入子報表物件
SubReportAverageScoreModel subReportAverageScoreModel = new SubReportAverageScoreModel(courseDesc, averageScore);
subReportModelList.add(subReportAverageScoreModel);
}
// 主報表物件
DepartmentCourseScoreAverageReportModel mainReportModel = new DepartmentCourseScoreAverageReportModel();
// 為了作為parametersMap的key，型別須轉為String
mainReportModel.setDepartmentId(departmentId.toString());
mainReportModel.setDepartmentDesc(departmentDesc);
mainReportModel.setSubReportAverageScoreModelList(subReportModelList);
mainReportModelList.add(mainReportModel);
}
List<DepartmentCourseScoreAverageReportModel> mainReportModelList =
reportDemoService.getDepartmentAverageScore();
Map<String, Object> parametersMap = this.getDateParameters();
private Map<String, Object> getDateParameters() {
Map<String, Object> parametersMap = new HashMap<>();
LocalDate localDate = new Date().toInstant()
.atZone(ZoneId.systemDefault()).toLocalDate();
parametersMap.put("date", localDate
.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日")));
}
子報表的部分只要先執行生命週期中的「將jrxml模板編譯成jasper文件」、「將Java集合資料來源與Jasper報表進行綁定」這兩步驟，之後再與主報表一起之行後續步驟。
P${compiledSubReport}，在這個步驟中可以把編譯後的子報表jasper文件放入REPORT_PARAMETERS_MAP。// 3. 設定子報表jasper文件
String subReportPath = "/Report/Jasper/DepartmentCourseScoreAverageSubReport.jrxml";
String expressionKey = "compiledSubReport";
try {
ExportReportUtil.setSubReportJasper(parametersMap, subReportPath, expressionKey);
} catch (Exception e) {
throw new RuntimeException(e);
}
public static void setSubReportJasper(Map<String, Object> parametersMap, String subReportExp, String expressionKey) throws Exception {
// jasper文件 子報表
try {
JasperReport subReport = JasperCompileManager
.compileReport(ExportReportUtil.class.getResourceAsStream(subReportExp));
// 將子報表放入主報表參數
parametersMap.put(expressionKey, subReport);
} catch (Exception e) {
throw new Exception();
}
}
$P{REPORT_PARAMETERS_MAP}.get($F{departmentId})，因此這個步驟必須將departmentId與對應的子報表DataSource放入parametersMap中，必須放入JRBeanCollectionDataSource，否則之後沒辦法直接將資料填入報表模板中。this.mapSubReportData(mainReportModelList, parametersMap);
private void mapSubReportData(List<DepartmentCourseScoreAverageReportModel> mainReportModelList, Map<String, Object> parametersMap) {
for (DepartmentCourseScoreAverageReportModel mainReportModel : mainReportModelList) {
String departmentId = mainReportModel.getDepartmentId();
List<SubReportAverageScoreModel> subReportModelList =
mainReportModel.getSubReportAverageScoreModelList();
// 設置為子報表的資料來源
JRDataSource subReportDataSource =
new JRBeanCollectionDataSource(subReportModelList);
parametersMap.put(departmentId, subReportDataSource);
}
}
主報表的部分只要用同樣的方法完成報表生命週期就好了。
// 5.匯出excel byte[]
byte[] bytes = null;
try {
String reportPath = "/Report/Jasper/DepartmentCourseScoreAverageReport.jrxml";
bytes = ExportReportUtil
.templateToExcelByte(mainReportModelList, reportPath, parametersMap);
} catch (Exception e) {
throw new RuntimeException(e);
}
public static byte[] templateToExcelByte(List dataSourceList, String reportPath, Map<String, Object> parametersMap) throws Exception {
try (ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream()) {
// 以JasperCompileManager將jrxml模板編譯成jasper文件
JasperReport jasperReport = JasperCompileManager
.compileReport(ExportReportUtil.class.getResourceAsStream(reportPath));
// 將Java集合資料來源與Jasper報表進行綁定
JRDataSource dataSource = new JRBeanCollectionDataSource(dataSourceList);
// 將資料填入報表
JasperPrint print = JasperFillManager
.fillReport(jasperReport, parametersMap, dataSource);
// 匯出
SimpleXlsxReportConfiguration xlsxReportConfiguration =
new SimpleXlsxReportConfiguration();
// setDetectCellType使excel偵測這個值的型別並轉換為對應的格式
xlsxReportConfiguration.setDetectCellType(true);
JRXlsxExporter exporter = new JRXlsxExporter();
exporter.setConfiguration(xlsxReportConfiguration);
exporter.setExporterInput(new SimpleExporterInput(print));
exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(byteArrayOutputStream));
exporter.exportReport();
return byteArrayOutputStream.toByteArray();
} catch (Exception e) {
throw new RuntimeException(e);
}
}
之後再將報表byte[]和檔案名稱處理就能生成excel檔了。
匯出之後可以發現資料已經照著一開始所想的格式排列，主報表的科系對應到子報表的課程與成績平均。只差最後一步了，要將主報表的科系欄位合併儲存格
合併儲存格的方法其實只要讓主報表欄位拓展到與對應子報表相同高度就好。
iThome鐵人賽
看影片追技術